"""
Case Type   : Function_hex
Case Name   : 使用hex函数查询表数据性能
Create At   : 2024/07/25
Owner       : lvlintao666
Description :
        1.创建表
        2.多线程向表中插入数据
        3.查看表中数据是否重复
        4.清理环境;删除表
Expect      :
        1.创建表成功
        2.插入数据成功
        3.表中数据没有重复
        4.删除成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

class UuidNotRepeat(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbBmode')
        self.table_name = 't_hex_020'
        self.procedure_name = 'f_inert_020'

    def test_uuid_not_repeat(self):
        text = '------step1:创建表;expect:成功------'
        self.log.info(text)
        create_cmd = f"drop table if exists t_hex_020;" \
                     f"create table {self.table_name}(c_01 text);"
        self.log.info(create_cmd)
        res = self.comsh.execut_db_sql(create_cmd)
        self.log.info(res)

        text = '------step2 创建存储过程插入数据;expect:成功------'
        self.log.info(text)
        create_pro_cmd = f"create or replace procedure {self.procedure_name} as " \
                         f"insert_str varchar(50);" \
                         f"end_time timestamp;" \
                         f"start_time timestamp;" \
                         f"time_time text;" \
                         f"begin " \
                         f"start_time := (select current_timestamp);" \
                         f"for i in 0..2000000 loop " \
                         f"insert_str = 'insert into {self.table_name} values(hex(12));';" \
                         f"execute immediate insert_str;" \
                         f"end loop;" \
                         f"end_time := (select current_timestamp);" \
                         f"time_time = end_time - start_time;" \
                         f"raise exception '%',(time_time);" \
                         f"end;"
        self.log.info(create_pro_cmd)
        create_procedure_result = self.comsh.execut_db_sql(create_pro_cmd)
        self.log.info(create_procedure_result)
        call_procedure_cmd = f"call {self.procedure_name}"
        self.log.info(call_procedure_cmd)
        execute_result = self.comsh.execut_db_sql(call_procedure_cmd)
        self.log.info(execute_result)

        text = '------step3:查询数据时间是否小于0.72s(经M*库测试时间约为0.72s);expect:成功------'
        self.log.info(text)
        select_cmd = f'explain analyze select hex(c_01) from {self.table_name};'
        select_result = self.comsh.execut_db_sql(select_cmd)
        self.log.info(select_result)
        time_res = select_result.splitlines()[-2].split('|')
        self.log.info(time_res)
        with_cache_time = time_res[0].split(':')[-1].split()[0].strip()
        self.log.info(with_cache_time)
        self.assertLess(float(with_cache_time), 720)

    def tearDown(self):
        text = '------step4:清理环境;expect:成功------'
        self.log.info(text)
        drop_cmd = f'drop table {self.table_name};'
        drop_msg = self.comsh.execut_db_sql(drop_cmd)
        self.log.info(drop_msg)
        drop_function_cmd = f'drop function {self.procedure_name};'
        drop_function_msg = self.comsh.execut_db_sql(drop_function_cmd)
        self.log.info(drop_function_msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_msg)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG,
                      drop_function_msg)
        self.log.info(f'----{os.path.basename(__file__)} end----')

